﻿CREATE PROCEDURE [dbo].[proc_Finance_Balance_Getlist]
	(
		@CompanyId VARCHAR(500),
		@StartDate nvarchar(10),
		@EndDate nvarchar(10),
		@Stext nvarchar(50),
		@StartIndex int,
		@EndIndex int
	)
AS
BEGIN
	DECLARE @strSql VARCHAR(4000)
	SET @strSql='
	;WITH list As(Select ROW_NUMBER() OVER (ORDER BY CreateDate)AS Row,
		Id,
		GId,
		Prices,
		TypeId,
		(select FA.title from Finance_AccountsType FA where FA.id = typeid) as Type,
		Reason,
		CurrentDate,
		CreateUser,
		CreateDate,
		CompanyId,
		(Select CompanyName From Company Where Id=Finance_Balance.CompanyId) As CompanyName,
		PaymentTypeId,
		BankId,
		(Select Title From Finance_Bank_Type Where Id=Finance_Balance.PaymentTypeId) As PaymentType,
		(Select OpenBank From Finance_Bank Where Id=Finance_Balance.BankId) As Bank
		From Finance_Balance
		Where ('''+@CompanyId+'''=''0'' Or CompanyId in ('+@CompanyId+')) and 
		(Convert(nchar(10),CurrentDate,120) between '''+@StartDate+''' and '''+@EndDate+''')
		and ('''+@Stext+'''='''' Or Reason like ''%'+@Stext+'%'')
	)
	
	
	Select * From list,(Select Count(Id) AS c_id,Sum(Prices) AS s_Prices From list
	) AS a Where Row between '+convert(varchar,@StartIndex)+' and '+convert(varchar,@EndIndex)+''
	 PRINT(@strSql)
	 EXEC(@strSql)
end
	RETURN
